package hw.pjbk;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 
 * @author Andrew
 * 
 *         Class handles installing database from assets folder including
 *         replacing older with latest versions Class also contains methods to
 *         return cursor objects for all questions, random subsets of questions
 *         and all answers for a question
 */

public class DatabaseHelper extends SQLiteOpenHelper {

	// database file name
	static final String DB_NAME = "quiz.sqlite";
	// latest db version
	public static final int DB_VERSION = 7;

	private String dbPath;
	private Context context;

	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);

		dbPath = "/data/data/" + context.getPackageName() + "/databases/";
		this.context = context;
		try {
			createDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// if no datababase exists, create an empty one and copy the DB file from
	// the assets folder, if it exists replace it if it's out of date
	public void createDataBase() throws IOException {
		int currentDBVersion = checkDB();
		if (currentDBVersion == -1) {
			// the DB doesn't exist yet, copy the file across
			Log.i("QUIZ", "Quiz DB does not exist, installing it.");
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}

		} else if (currentDBVersion < DB_VERSION) {
			// the database already exists but is older than
			// the latest one, upgrade it
			Log.i("QUIZ",
					"Quiz DB already exists but is out of date, upgrading it...");
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}

		} else {
			// the database already exists and does not need upgrading
			// simply log this fact
			Log.i("QUIZ", "Up to date version of DB already exists.");
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i("QUIZ", "onCreate method called");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("QUIZ", "onUpgrade method called");

	}

	// copy DB file over existing or new blank DB
	private void copyDataBase() throws IOException {
		// Open local db as the input stream
		InputStream input = context.getAssets().open(DB_NAME);

		// Open the empty db as the output stream
		OutputStream output = new FileOutputStream(dbPath + DB_NAME);

		// transfer input to output
		byte[] buffer = new byte[1024];
		int length;
		while ((length = input.read(buffer)) > 0) {
			output.write(buffer, 0, length);
		}
		// Close the streams
		output.flush();
		output.close();
		input.close();
	}

	// check to see if the DB already exists, if it does get it's version
	private int checkDB() {
		SQLiteDatabase checkDB = null;
		int dbVersion = -1;
		try {
			// try to connect to the DB file
			Log.i("QUIZ", "Trying to connect to existing DB file");
			checkDB = SQLiteDatabase.openDatabase(dbPath + DB_NAME, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (Exception e) {
			// database does't exist yet.
			Log.i("QUIZ", "Database does't exist yet");
		}
		if (checkDB != null) {
			// as the DB does exist ask it what version it is...
			Log.i("QUIZ", "DB does exist ask it what version it");
			dbVersion = checkDB.getVersion();
			Log.i("QUIZ", "Existing DB is version " + dbVersion);
			checkDB.close();
		}
		// return the db version or -1 if no db found
		return dbVersion;
	}

	// get all possible answers for the question specified by its ID
	public Cursor getAnswers(String QuestionID) {

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT _id, AnswerText "
				+ " from Answers WHERE QuesstionID = ?",
				new String[] { QuestionID });

		return cur;

	}

	// get all questions
	public Cursor getQuestions() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery(
				"SELECT _id, QuestionText, CorrectAnswerID FROM Questions;",
				null);

		return cur;

	}

	// get a quantity of random questions
	public Cursor getRandomQuestions(int numberOfQuestions) {
		String randomQs = selectRandomQuestionIDs(numberOfQuestions);
		;
		Log.i("HIST", "Getting questions with IDs " + randomQs);
		String query = "SELECT _id, QuestionText, CorrectAnswerID FROM Questions "
				+ " WHERE _id IN " + randomQs;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery(query, null);
		cur.moveToFirst();
		return cur;

	}

	// get the IDs of a random selection of questions to be used in the query in
	// the method getRandomQuestions above
	private String selectRandomQuestionIDs(int numberOfQuestions) {
		// find out how many questions there are in the database
		SQLiteDatabase db = this.getReadableDatabase();
		Log.i("HIST", "Getting total number of Qs");
		Cursor cur = db.rawQuery("SELECT COUNT(_id) AS count FROM Questions;",
				null);
		cur.moveToFirst();
		int totalNoOfQuestions = Integer.parseInt(cur.getString(cur
				.getColumnIndexOrThrow("count")));

		if (totalNoOfQuestions < numberOfQuestions) {
			numberOfQuestions = totalNoOfQuestions;
		}

		Log.i("HIST", "Creating ArrayList");
		ArrayList<Integer> questionIDs = new ArrayList<Integer>();
		for (int i = 0; i < totalNoOfQuestions; i++) {
			questionIDs.add(i + 1);

		}

		// shuffle the questions
		Collections.shuffle(questionIDs);

		// leave only the required amount of questions, remove the rest
		if (numberOfQuestions < totalNoOfQuestions) {
			for (int i = totalNoOfQuestions; i > numberOfQuestions; i--) {
				questionIDs.remove(i - 1);

			}
		}

		// return the IDs of the questions in a form that can be used in a SQL
		// select statement
		String returnString = questionIDs.toString();
		Log.i("HIST", "Formatting string");
		returnString = "("
				+ returnString.substring(1, returnString.length() - 1) + ")";
		return returnString;

	}
}
